﻿<cfsilent>
	<cfscript>

		datetimeAdvice = getProperty("serviceFactory").getBean("datetimeAdvice");
		propAdvice = getProperty("serviceFactory").getBean("studentPropertyAdvice");
		sessionAdvice = getProperty("serviceFactory").getBean("sessionAdvice");

		depId = sessionAdvice.getUserProp("teacherDepartment");
		
		subjectId = event.getArg('SubID');
		grade = event.getArg('Grade');
		
		sql = "SELECT sbj_name FROM t_subject WHERE sbj_id = :subjectId AND institute_id = :depId";
		
		queryObj = new Query( datasource=application.dnsSlave );
		queryObj.addParam( name="subjectId", value=subjectId, cfsqltype = "cf_sql_varchar");
		queryObj.addParam( name="depId", value=depId, cfsqltype = "cf_sql_varchar");

		rs_subject = queryObj.execute( sql=sql ).getResult();


		temFile = GetTempDirectory() & createUUID() & ".xls";
		downFile = URLEncodedFormat( rs_subject.sbj_name & "专业," & grade & "级-学分统计", "utf-8");
		
		/* 读取班级学生分数统计 */
		sql = "SELECT 
				a.stu_id, a.stu_name, a.stu_sex, 
				b.cls_name, b.grade, 
				c.sbj_name, 
				d.institute_name, 
				e.credit, e.credit_req, e.credit_opt, e.credit_phy, e.credit_com, e.credit_failure,
				e.credit_req_pt, e.credit_req_avgpt, 
				e.credit_pt, e.credit_avgpt, 
				e.subject_rank, e.subject_idx, 
				e.subject_rank_all, e.subject_idx_all, 
				f.student_prop 
				FROM t_student a 
				INNER JOIN t_class b ON b.cls_id = a.cls_id 
				INNER JOIN t_subject c ON c.sbj_id = b.sbj_id 
				INNER JOIN t_institute d ON d.institute_id = c.institute_id 
				INNER JOIN t_student_profile e ON e.stu_id = a.stu_id 
				INNER JOIN t_student_status f ON f.stu_id = a.stu_id 
				WHERE 
					b.sbj_id = :subjectId 
					AND 
					b.grade = :grade 
					AND 
					d.institute_id = :depId 
				ORDER BY a.stu_id ";
		
		queryObj = new Query( datasource=application.dnsSlave );
		queryObj.addParam( name="subjectId", value=subjectId, cfsqltype = "cf_sql_varchar");
		queryObj.addParam( name="grade", value=grade, cfsqltype = "cf_sql_char");
		queryObj.addParam( name="depId", value=depId, cfsqltype = "cf_sql_varchar");

		rs_student = queryObj.execute( sql=sql ).getResult();


		excel = getProperty("serviceFactory").getBean("spreadSheetObject");

		/* 创建工作簿 */
		spreadsheetObj = excel.SpreadSheetNew( rs_subject.sbj_name & "专业学分统计", false);

		/* 添加表头 */
		excel.SpreadsheetAddrow(spreadsheetObj, "学号,姓名,性别,班级,专业,年级,学院,总学分,必修,限选,任选,体育,不及格,必修课平均绩点,课程平均绩点,在校,必修课专业排名,全部课程排名");
		
		row = 1;
		
		for ( a=1; a LTE rs_student.recordCount; a++ ) {
			
			row++;
			
			propAdvice.parseProperty( rs_student["student_prop"][a] );

			/* 构建学生信息 */
			rowData = rs_student["stu_id"][a]
						& "," & rs_student["stu_name"][a];
						
			if ( rs_student["stu_sex"][a] eq "1" ) {
				rowData = rowData & ",男";
			}
			else {
				rowData = rowData & ",女";
			}
			
			rowData = rowData & "," & rs_student["cls_name"][a] & "," & rs_student["sbj_name"][a] & "," & rs_student["grade"][a] & "," & rs_student["institute_name"][a];
			
			if ( rs_student["credit"][a] gt 0 ) {
				rowData = rowData & "," & numberFormat( rs_student["credit"][a], "_.__");				
			}
			else {
				rowData = rowData & ", ";				
			}
			if ( rs_student["credit_req"][a] gt 0 ) {
				rowData = rowData & "," & numberFormat( rs_student["credit_req"][a], "_.__");				
			}
			else {
				rowData = rowData & ", ";				
			}
			if ( rs_student["credit_opt"][a] gt 0 ) {
				rowData = rowData & "," & numberFormat( rs_student["credit_opt"][a], "_.__");				
			}
			else {
				rowData = rowData & ", ";				
			}
			if ( rs_student["credit_com"][a] gt 0 ) {
				rowData = rowData & "," & numberFormat( rs_student["credit_com"][a], "_.__");				
			}
			else {
				rowData = rowData & ", ";				
			}
			if ( rs_student["credit_phy"][a] gt 0 ) {
				rowData = rowData & "," & numberFormat( rs_student["credit_phy"][a], "_.__");				
			}
			else {
				rowData = rowData & ", ";				
			}
			if ( rs_student["credit_failure"][a] gt 0 ) {
				rowData = rowData & "," & numberFormat( rs_student["credit_failure"][a], "_.__");				
			}
			else {
				rowData = rowData & ", ";				
			}
			if ( rs_student["credit_req_avgpt"][a] gt 0 ) {
				rowData = rowData & "," & numberFormat( rs_student["credit_req_avgpt"][a], "_.___");				
			}
			else {
				rowData = rowData & ", ";				
			}
			
			if ( rs_student["credit_avgpt"][a] gt 0 ) {
				rowData = rowData & "," & numberFormat( rs_student["credit_avgpt"][a], "_.___");				
			}
			else {
				rowData = rowData & ", ";				
			}
			
			if ( propAdvice.getInSchool() ) {
				rowData = rowData & ",是";
			}
			else {
				rowData = rowData & ",否";
			}
			
			if ( rs_student["subject_idx"][a] gt 0 ) {
				rowData = rowData & "," & rs_student["subject_idx"][a];				
			}
			else {
				rowData = rowData & ", ";				
			}
			
			if ( rs_student["subject_idx_all"][a] gt 0 ) {
				rowData = rowData & "," & rs_student["subject_idx_all"][a];				
			}
			else {
				rowData = rowData & ", ";				
			}
			
			/* 写入学生信息 */
			excel.SpreadsheetAddrow(spreadsheetObj, rowData);
			
		}

									
		excel.SpreadSheetWrite(spreadsheetObj, temFile, true);

	</cfscript>

	<cfheader name="Content-Disposition" value="attachment; filename=#downFile#.xls" />
	<cfcontent file="#temFile#" reset="yes" type="application/msexcel" deletefile="yes" />

</cfsilent>